package nsn.excel;

import jdk.nashorn.tools.Shell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.rmi.server.ExportException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


public abstract class AbstractExcel implements Excel {
    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";
    private static SimpleDateFormat formatDateTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private static Pattern CONTENTMATCHER = Pattern.compile("\\$\\{(.*?)}");
    private static String[] sources = new String[]{
            "A", "B", "C", "D", "E", "F", "G", "H",
            "I", "J", "K", "L", "M", "N", "O", "P",
            "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"
    };
    // 日志打印类
    private static Logger logger = Logger.getLogger(AbstractExcel.class.getName());


    @Override
    public List<Sheet> getSheetList(InputStream inputStream, String suffix) {
        List<Sheet> shells = null;
        try {
            shells = new ArrayList<>();
            Workbook workbook = getWorkbook(inputStream, suffix);
            for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
                Sheet sheet = workbook.getSheetAt(sheetNum);
                // 校验sheet是否合法
                if (sheet == null) {
                    continue;
                }
                shells.add(sheet);
            }
            if (shells.size() <= 0) {
                logger.log(Level.ALL, "Excel中的Sheet表为空");
                throw new FileNotFoundException();
            }
        } catch (IOException e) {
            logger.log(Level.ALL, "文件解析成WorkBook解析失败");
            e.printStackTrace();
        }

        return shells;
    }

    @Override
    public Sheet getSheet(InputStream inputStream, String suffix, Integer sheetNo) {
        Sheet sheetAt = null;
        try {
            Workbook workbook = getWorkbook(inputStream, suffix);
            sheetAt = workbook.getSheetAt(sheetNo);
            if (sheetAt == null) {
                logger.log(Level.ALL, "该sheet不存在");
                throw new FileNotFoundException();
            }
        } catch (IOException e) {
            logger.log(Level.ALL, "文件解析成WorkBook解析失败");
            e.printStackTrace();
        }
        return sheetAt;
    }


    @Override
    public List<Map<String, Object>> getSheetMap(Sheet sheet) {
        List<Map<String, Object>> datas = new ArrayList<>();
        // 解析每一行的数据，构造数据对象
        int rowEnd = sheet.getPhysicalNumberOfRows();
        //获取列名称 fields 来赋值给Map
        String[] fields = null;
        for(int filesNum=0;filesNum<sheet.getPhysicalNumberOfRows();filesNum++){
            Row tempRow = sheet.getRow(filesNum);
            if(tempRow!=null){
                fields = getColumnLabels(tempRow.getPhysicalNumberOfCells());
                break;
            }
        }
        //解析行数据装入Map
        for (int rowNum = 0; rowNum < rowEnd; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (null == row) {
                //如果是空行 继续累加 数据和界面展示一摸一样
                Map<String, Object> dataMap = new HashMap<>();
                for (int i = 0; i < fields.length; i++) {
                    dataMap.put(fields[i], null);
                }
                datas.add(dataMap);
                rowEnd += 1;
                continue;
            }
            //封装数据的map
            Map<String, Object> dataMap = new HashMap<>();
            for (int i = 0; i < fields.length; i++) {
                String data = convertCellValueToString(row.getCell(i));
                dataMap.put(fields[i], data);
            }
            datas.add(dataMap);
        }
        return datas;
    }

    @Override
    public List<Row> getSheetRow(Sheet sheet) {
        List<Row> rows = new ArrayList<>();
        int firstRowNum = sheet.getFirstRowNum();
        Row firstRow = sheet.getRow(firstRowNum);
        if (null == firstRow) {
            logger.warning("在第一行没有读取到任何数据！");
        }
        // 解析每一行的数据，构造数据对象
        int rowStart = firstRowNum;
        int rowEnd = sheet.getPhysicalNumberOfRows();
        //获取列名称 fields 来赋值给Map
        for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (null == row) {
                rowEnd += 1;
                continue;
            }
            rows.add(row);
        }
        return rows;
    }


    /**
     * 有可能存在多个变量 所以切割一下
     * 原理 数组对照 可能存在值和分隔符一样 导致解析问题
     * @param model
     * @param data
     * @return
     * @author caq
     */
    public Map<String, Object> split(String model, String data) {
        Matcher matcher = CONTENTMATCHER.matcher(model);
        Map<String, Object> resultMap = new HashMap<>();
        if (data == null) {
            while (matcher.find()) {
                String name = matcher.group(0);
                resultMap.put(name.substring(2, name.length() - 1), null);
            }
            return resultMap;
        }
        //内容匹配
        String placeholder = "#{caq-nsn-a7}";
        //取出需要替换掉的内容
        String[] modelSplit = CONTENTMATCHER.split(model);
        for (String s : modelSplit) {
            if (s.equals("")) continue;
            data = data.replace(s, placeholder);
        }
        if (data.indexOf(placeholder) == 0) {
            data = data.substring(placeholder.length());
        }
        if (data.length() == (data.lastIndexOf(placeholder) + placeholder.length())) {
            data = data.substring(0, data.lastIndexOf(placeholder));
        }
        String[] values = data.split("#\\{caq-nsn-a7}");
        int i = 0;
        while (matcher.find()) {
            String name = matcher.group(0);
            resultMap.put(name.substring(2, name.length() - 1), values[i++]);
        }
        return resultMap;
    }


    /**
     * (256 for *.xls, 16384 for *.xlsx)
     *
     * @param columnNum 列的个数，至少要为1
     * @return 返回[1, columnNum]共columnNum个对应xls列字母的数组
     * @throws IllegalArgumentException 如果 columnNum 超出该范围 [1,16384]
     */
    private static String[] getColumnLabels(int columnNum) {
        if (columnNum < 1 || columnNum > 16384)
            throw new IllegalArgumentException();
        String[] columns = new String[columnNum];
        if (columnNum < 27) {    //小于27列 不用组合
            System.arraycopy(sources, 0, columns, 0, columnNum);
            return columns;
        }
        System.arraycopy(sources, 0, columns, 0, 26);    //前26列不需要进行组合

        //因为基于数组是从0开始，每到新一轮letterIdx 会递增，所以第一轮 在递增前是-1
        int letterIdx = -1;
        int currentLen = 26;//第一轮组合(2个字母的组合)是分别与A-Z进行拼接 所以是26
        int remainder;
        int lastLen = 0;    //用于定位上文提到的i%currentLen实际在数组中的位置
        int totalLen = 26;    //totalLen=currentLen+lastLen
        int currentLoopIdx = 0; //用来记录当前组合所有情形的个数

        for (int i = 26; i < columnNum; i++) { //第27列(对应数组的第26个位置)开始组合
            //currentLen是上个组合所有情形的个数，与它取余找到要与上个组合的哪种情形进行拼接
            remainder = currentLoopIdx % currentLen;
            if (remainder == 0) {
                letterIdx++; //完成一次上个组合的遍历，转到下个字母进行拼接
                int j = letterIdx % 26;
                //A-Z 26个子母都与上个组合所有情形都进行过拼接了，需要进行下个组合的拼接
                if (j == 0 && letterIdx != 0) {
                    lastLen = totalLen; //下个组合的lastLen是上个组合的totalLen
                    /**
                     * 下个组合的currentLen是上个组合的所有组合情形的个数
                     * （等于上个组合的currentLen*26)，26也就是拼接在前面的A-Z的个数
                     */
                    currentLen = 26 * currentLen;
                    totalLen = currentLen + lastLen; //为下一轮的开始做准备
                    currentLoopIdx = 0; //到下一轮了 因此需要重置
                }
            }
            /**
             * sources[letterIdx%26]是该轮要拼接在前面的字母
             * columns[remainder+lastLen]是上个组合被拼接的情形
             */
            columns[i] = sources[letterIdx % 26] + columns[remainder + lastLen];
            currentLoopIdx++;
        }
        return columns;
    }

    /**
     * 将单元格内容转换为字符串
     *
     * @param cell
     * @return
     * @author caq
     */
    private static String convertCellValueToString(Cell cell) {
        if (cell == null) {
            return null;
        }
        String returnValue = null;
        try {

            switch (cell.getCellType()) {
                case NUMERIC:   //数字 和日期
                    //时间
                    if (DateUtil.isCellDateFormatted(cell)) {
                        Date dateCellValue = cell.getDateCellValue();
                        returnValue = formatDateTime.format(dateCellValue);
                    } else {
                        Double doubleValue = cell.getNumericCellValue();
                        DecimalFormat df = new DecimalFormat("0");
                        returnValue = df.format(doubleValue);
                    }
                    break;
                case STRING:    //字符串
                    returnValue = cell.getStringCellValue();
                    break;
                case BOOLEAN:   //布尔
                    Boolean booleanValue = cell.getBooleanCellValue();
                    returnValue = booleanValue.toString();
                    break;
                case BLANK:     // 空值
                    break;
                case FORMULA:   // 公式
                    returnValue = cell.getCellFormula();
                    break;
                case ERROR:     // 故障
                    break;
                default:
                    break;
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return returnValue;
    }

    public Workbook getWorkbook(InputStream inputStream, String suffix) {
        Workbook workbook = null;
        try {
            if (suffix.equalsIgnoreCase(XLS)) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (suffix.equalsIgnoreCase(XLSX)) {
                workbook = new XSSFWorkbook(inputStream);
            }else {
                logger.log(Level.ALL, "解析失败没有找到 后缀为"+suffix+"的文件流");
            }
        } catch (IOException e) {
            logger.log(Level.ALL, "Excel解析Sheet异常");
            e.printStackTrace();
        }

        return workbook;
    }


}
